package com.dxl.common.utils;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;


/**
 * EXCEL读写工具类
 * @author 邓小林
 */
public class ExcelUtils {
	private static final Logger LOG = Logger.getLogger(ExcelUtils.class);
	
	public static Integer getInteger(Sheet sheet, int row, int col) {
		if (Utils.isNull(sheet) || row < 0 || col < 0) {
			return null;
		}
		return getInteger(sheet.getRow(row), col);
	}
	
	public static Integer getInteger(Row row, int col) {
		if (Utils.isNull(row) || col < 0) {
			return null;
		}
		return getInteger(row.getCell(col));
	}
	
	public static Integer getInteger(Cell cell) {
		if (Utils.isNull(cell)) {
			return null;
		}
		try {
			return getDouble(cell).intValue();
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
			return Utils.toInt(getString(cell));
		}
	}
	
	public static BigDecimal getBigDecimal(Sheet sheet, int row, int col) {
		if (Utils.isNull(sheet) || row < 0 || col < 0) {
			return null;
		}
		return getBigDecimal(sheet.getRow(row), col);
	}
	
	public static BigDecimal getBigDecimal(Row row, int col) {
		if (Utils.isNull(row) || col < 0) {
			return null;
		}
		return getBigDecimal(row.getCell(col));
	}
	
	public static BigDecimal getBigDecimal(Cell cell) {
		if (Utils.isNull(cell)) {
			return null;
		}
		try {
			return Utils.toBigDecimal(getString(cell));
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
			return new BigDecimal(getDouble(cell));
		}
	}
	
	public static Double getDouble(Sheet sheet, int row, int col) {
		if (Utils.isNull(sheet) || row < 0 || col < 0) {
			return null;
		}
		return getDouble(sheet.getRow(row), col);
	}
	
	public static Double getDouble(Row row, int col) {
		if (Utils.isNull(row) || col < 0) {
			return null;
		}
		return getDouble(row.getCell(col));
	}
	
	public static Double getDouble(Cell cell) {
		if (Utils.isNull(cell)) {
			return null;
		}
		try {
			return cell.getNumericCellValue();
		} catch (Exception e) {
			LOG.error(e.getMessage(), e);
			return Utils.toDouble(getString(cell));
		}
	}
	
	public static String getString(Sheet sheet, int row, int col) {
		if (Utils.isNull(sheet) || row < 0 || col < 0) {
			return "";
		}
		return getString(sheet.getRow(row), col);
	}
	
	public static String getString(Row row, int col) {
		if (Utils.isNull(row) || col < 0) {
			return "";
		}
		return getString(row.getCell(col));
	}
	
	public static String getString(Cell cell) {
		if (Utils.isNull(cell)) {
			return "";
		}
		int cellType = cell.getCellType();
		NumberFormat nf = new DecimalFormat("#.#########");
		switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC: {
				return nf.format(cell.getNumericCellValue());
			}
			default: {
				String value = cell.getStringCellValue();
				return Utils.isNotNull(value) ? value.trim() : null;
			}
		}
	}
	
	public static void setString(Row row, int col, String value) {
		if (Utils.isNull(row) || col < 0) {
			return;
		}
		Cell cell = row.getCell(col);
		if (Utils.isNull(cell)) {
			cell = row.createCell(col);
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		cell.setCellValue(value);
	}
}
